﻿using Microsoft.EntityFrameworkCore;
using NorthwindDataservice.Models;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace NorthwindDataservice
{
    /**
     * Class dealing with the Data Service layer functionality.
     */
    public class DataService
    {
        /**
         * Retrieves all the categories from the database.
         */
        public List<Category> GetCategories()
        {
            // Spawning a new NorthwindContext, with automatic object disposal handling.
            // @see https://docs.microsoft.com/en-us/dotnet/csharp/language-reference/keywords/using-statement
            using(var db = new NorthwindDbContext())
            {
                IQueryable<Category> categories = db.Categories;
                return categories.ToList();
            }
        }

        /**
         * Retrieves a category by id.
         */
        public Category GetCategory(int id)
        {
            using (var db = new NorthwindDbContext())
            {
                return db.Categories.SingleOrDefault(c => c.Id == id);
            }
        }

        /**
         * Creates a category with a given name and description.
         */
        public Category CreateCategory(string name, string description)
        {
            using (var db = new NorthwindDbContext())
            {
                var category = new Category();
                category.Name = name;
                category.Description = description;

                int newId = db.Categories.Max(c => c.Id) + 1;
                category.Id = newId;

                db.Categories.Add(category);
                db.SaveChanges();

                return category;
            }
        }

        /**
         * Removes a category by id.
         */
        public bool DeleteCategory(int id)
        {
            using (var db = new NorthwindDbContext())
            {
                var category = db.Categories.SingleOrDefault(c => c.Id == id);

                if (category == null)
                {
                    return false;
                }

                db.Categories.Remove(category);
                db.SaveChanges();

                return true;
            }
        }

        /**
         * Updates a category given id, name and description.
         */
        public bool UpdateCategory(int id, string name, string description)
        {
            using (var db = new NorthwindDbContext())
            {
                var category = db.Categories.SingleOrDefault(c => c.Id == id);

                if (category == null)
                {
                    return false;
                }

                category.Name = name;
                category.Description = description;
                db.SaveChanges();

                return true;
            }
        }

        /**
         * Retrieves all the orders in the database.
         */
        public List<Order> GetOrders()
        {
            using (var db = new NorthwindDbContext())
            {
                IQueryable<Order> orders = db.Orders;
                return orders.ToList();
            }
        }

        /**
         * Retrieves an order with the given order id or the default value.
         */
        public Order GetOrder(int id)
        {
            using (var db = new NorthwindDbContext())
            {
                return db.Orders
                    .Where(o => o.Id == id)
                    .Include(o => o.OrderDetails)
                        .ThenInclude(od => od.Product)
                            .ThenInclude(p => p.Category)
                    .SingleOrDefault();
            }
        }

        /**
         * Retrieves order details of a given order id.
         */
        public List<OrderDetails> GetOrderDetailsByOrderId(int orderId)
        {
            using (var db = new NorthwindDbContext())
            {
                IQueryable<OrderDetails> orderDetails = db.OrderDetails.Where(od => od.OrderId == orderId).Include(od => od.Order).Include(od => od.Product).ThenInclude(p => p.Category);
                return orderDetails.ToList();
            }
        }

        /**
         * Retrieves order details of a given product id.
         */
        public List<OrderDetails> GetOrderDetailsByProductId(int productId)
        {
            using (var db = new NorthwindDbContext())
            {
                IQueryable<OrderDetails> orderDetails = db.OrderDetails.Where(od => od.ProductId == productId).Include(od => od.Product).Include(od => od.Order).OrderBy(od => od.OrderId);
                return orderDetails.ToList();
            }
        }

        /**
         * Retrieves a product by id.
         */
        public Product GetProduct(int id)
        {
            using (var db = new NorthwindDbContext())
            {
                return db.Products.Where(p => p.Id == id).Include(p => p.Category).Include(p => p.OrderDetails).SingleOrDefault();
            }
        }

        /**
         * Retrieves a list of products related with a given category id.
         */
        public List<Product> GetProductByCategory(int categoryId)
        {
            using (var db = new NorthwindDbContext())
            {
                IQueryable<Product> products = db.Products.Where(p => p.Category.Id == categoryId).Include(p => p.Category);
                return products.ToList();
            }
        }

        /**
         * Retrieves a list of products with a given name.
         */
        public List<Product> GetProductByName(string name)
        {
            using (var db = new NorthwindDbContext())
            {
                IQueryable<Product> products = db.Products.Where(p => p.Name.ToLower().Contains(name.ToLower()));
                return products.ToList();
            }
        }
    }
}
